import pymysql
import pandas as pd

class MysqlUtils(object):
    """数据库工具类

    Args:
        object (_type_): _description_
    """

    
    def __init__(self):
        self.conn = pymysql.connect(
            host='127.0.0.1',
            users='root',
            passwd='root',
            db='tushare',
            port=3306,
            charset='utf=8'
        )

class classIfication(object):
    """分类相关类

    Args:
        object (_type_): _description_
    """
    def __init__(self):
        pass

    def get_fina_indicator(self,conn):
        """获取财务数据
        """

        cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)

        sql="""
        SELECT ts_code, ann_date, eps, total_revenue_ps,undist_profit_ps,gross_margin, fcff, fcfe, tangible_asset, bps,grossprofit_margin,
        npta FROM financial_data WHERE ann_date >='2023-01-01' and ann_date < '2024-01-01'
        """
        cursor.execute(sql)
        ret=cursor.fetchall()
        df=pd.DataFrame(ret)
        df1=df.dropna(subset=['eps','total_revenue_ps','gross_margin','fcff','fcfe','tangible_asset','bps','grossprofit_margin'])
        #重建索引
        df1=df1.reset_index(drop=True)
        
        print(ret)

if __name__=='__main__':
    mu=MysqlUtils()
    ci=classIfication()
    ci.get_fina_indicator(mu.conn)
